package com.plantplaces.dao;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.plantplaces.dto.Plant;
import com.plantplaces.dto.PlantSearch;

/**
 * Communicate with our local SQLite data.
 * 
 * @author jonesbr
 *
 */
public class OfflinePlantDAO extends SQLiteOpenHelper implements IOfflinePlantDAO {

	private static final String GUID = "guid";
	private static final String EDIBLE = "edible";
	private static final String RECOMMENDED = "recommended";
	private static final String COMMON = "common";
	private static final String CULTIVAR = "cultivar";
	private static final String SPECIES = "species";
	private static final String GENUS = "genus";
	private static final String PLANTS_TABLE = "plants";

	public OfflinePlantDAO(Context context) {
		super(context, "plantplaces", null, 1);
		
	}
	
	@Override
	public List<Plant> fetchPlants(PlantSearch plantSearch) throws Exception  {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void insert(Plant plant) throws Exception {
		// assemble a ContentValues collection of column:value pairs.
		ContentValues values = new ContentValues();
		values.put(GUID, plant.getGuid());
		values.put(GENUS, plant.getGenus());
		values.put(SPECIES, plant.getSpecies());
		values.put(COMMON, plant.getCommon());
		values.put(CULTIVAR, plant.getCultivar());
		
		// write data to the database.
		long id = getWritableDatabase().insert(PLANTS_TABLE, GENUS, values);
		
		// we just got back the UID from the local database.  Store this into the current plant object.
		plant.setId((int) id);
		
		
		
		
	}
	
	

	@Override
	public void onCreate(SQLiteDatabase db) {
		// Create SQL for a database table.
		String sql = "CREATE TABLE " + PLANTS_TABLE + " ( _id INTEGER PRIMARY KEY AUTOINCREMENT, " + GUID + " INTEGER, " 
				+ GENUS + " TEXT, " + 	SPECIES + " TEXT, " + CULTIVAR + " TEXT, " + COMMON + " TEXT, " 
				+ RECOMMENDED + " INTEGER, " + EDIBLE + " INTEGER " + ");";
		// run the sql to create a database table.
		db.execSQL(sql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public Plant fetchPlantByPlantPlacesId(int id) throws Exception {
		Plant thisPlant = null;
	
		// assemble a SQL statement to run our query.
		String fetchSQL = "SELECT * FROM " + PLANTS_TABLE + " WHERE " + GUID + " = " + id;
		
		// run the query.  Cursor is a result set from a query that gives us results back.
		Cursor cursor = getReadableDatabase().rawQuery(fetchSQL, null);
		
		// Do we even have results?
		if (cursor.getCount() > 0) {
			// move to the first record.
			cursor.moveToFirst();
			
			// iterate over the cursor and store the values in our plant.
			thisPlant = new Plant();
			
			// populate our newly created plant.
			thisPlant.setId(cursor.getInt(0));
			thisPlant.setGuid(cursor.getInt(1));
			thisPlant.setGenus(cursor.getString(2));
			thisPlant.setSpecies(cursor.getString(3));
			thisPlant.setCultivar(cursor.getString(4));
			thisPlant.setCommon(cursor.getString(5));
			
			// close the cursor and free up memory.
			cursor.close();
		}
		
		return thisPlant;
	}

	@Override
	public ArrayList<String> fetchGenus() {
		// declare our return value.
		ArrayList<String> allGenus = new ArrayList<String>();
		
		// the SQL query to select all unique genus.
		String selectGenus = "SELECT DISTINCT " + GENUS + " FROM " + PLANTS_TABLE;
		
		// run the query.
		Cursor cursor = getReadableDatabase().rawQuery(selectGenus, null);
		
		// do we have at least one result?
		if (cursor.getCount() > 0) {
			// go to the first result.
			cursor.moveToFirst();
			
			// iterate over the result.
			while (!cursor.isAfterLast()) {
				// add the result to the collection.
				 allGenus.add(cursor.getString(0));
				 
				 // move to the next row.
				 cursor.moveToNext();
			}

		}
		
		// return the results.
		return allGenus;
	}

}
